#!/usr/bin/python
# -*- coding: utf-8 -*-

import SimpleHTTPServer
import SocketServer
from urlparse import urlparse, parse_qs
import sqlite3 as sqlite
from xlrd import *
import csv
import RPi.GPIO as GPIO
import threading, Queue
import time
from datetime import datetime
import urllib2
import pexpect
import GmailManager
import sys

work = Queue.Queue(0)
_const = {}

class Command():
    def __init__(self, ctype, info, st, fin):
        self.cmd_type = ctype
        self.cmd_info = info
        self.cmd_st = st
        self.cmd_fin = fin
        self.err_count = 0

class Command_Thread(threading.Thread):
   
    sql_thread = None

    def init(self, tid):
        self.sql_thread = tid
        
    def cmd_excel_convert(self, filename):
        #filename = token[1]
        xlsbook = open_workbook("/usr/share/nginx/www/" + filename)
        xlssheet = xlsbook.sheet_by_name('personaldata')
        userlist = []
        SID = 0
        CID = 0
        name = 0
        gender = 0
        department = 0
        height = 0
        weight = 0
        email = 0
        TagID = 0
        for col in range(xlssheet.ncols):
            column_name = xlssheet.cell_value(0, col)
            if column_name == u'學號':
                SID = col
            elif column_name == u'學校代碼':
                CID = col
            elif column_name == u'姓名':
                name = col
            elif column_name == u'性別':
                gender = col
            elif column_name == u'科系':
                department = col
            elif column_name == u'身高':
                height = col
            elif column_name == u'體重':
                weight = col
            elif column_name == u'電子郵件信箱':
                email = col
            else:
                TagID = col
        
        for row in range(1, xlssheet.nrows):
            try:
                this_row = [None, None, None, None, None, None, None, None, None]
                this_row[0] = xlssheet.cell_value(row, SID)
                this_row[1] = xlssheet.cell_value(row, CID)
                this_row[2] = xlssheet.cell_value(row, name)
                this_row[3] = 'M' if xlssheet.cell_value(row, gender) == u'男' else 'F'
                this_row[4] = xlssheet.cell_value(row, department)
                this_row[5] = xlssheet.cell_value(row, height)
                this_row[6] = xlssheet.cell_value(row, weight)
                this_row[7] = xlssheet.cell_value(row, email)
                this_row[8] = xlssheet.cell_value(row, TagID)
                print this_row
                userlist.append(this_row)
            except:
                print 'format error'
        
        #global sql_thread
        self.sql_thread.insert_userinfo(userlist)
        
    def resetTimer(self):
        work.put(Command('reset_timer', 'now', 0, 23))
        
    def addwork(self, newwork):
        for item in newwork:
            work.put(item)
        
    def send_error_mail(self, title, content):
        mail = _const("mailuser")
        print('sent error mail to %s' % mail)
        GmailManager.sendGmailSmtp(mail, _const("mailpassword"), mail, title, content)


    def check_internet(self, ip):
        ssh = pexpect.spawn('ping ' + ip + ' -c 3')
        index = ssh.expect(['100%', '0%'])
        if index == 0:
            return True
        elif index == 1:
            return False
            

    def run(self):
        while True:
            if work.empty():
                time.sleep(1)
                continue
            
            cmd = work.get()
            if cmd.cmd_type == 'xls':
                try:
                    self.cmd_excel_convert(cmd.cmd_info)
                except:
                    print 'excel convert error'
                                 
            elif cmd.cmd_type == 'timer':
                hour = time.localtime().tm_hour
                if cmd.cmd_st == hour:
                    try:
                        f = urllib2.urlopen("http://" + cmd.cmd_info + ":8011/?relay=on")
                        respone = f.read()
                        if respone == 'relay on':
                            work.put(Command('timer', cmd.cmd_info, -1, cmd.cmd_fin))
                        else:
                            #work.put(cmd)                            
                            if self.check_internet(cmd.cmd_info) == False:
                                self.send_error_mail('client disconnect', 'relay on fail')
                                
                    except:                        
                        self.send_error_mail('client disconnect', str(sys.exc_info()))
                        print sys.exc_info()
                    
                elif cmd.cmd_fin == hour:
                    try:
                        f = urllib2.urlopen("http://" + cmd.cmd_info + ":8011/?relay=off")
                        if f.read() == 'relay off':
                            print 'relay off confirm'
                            #work.put(Command('prepare', cmd.cmd_info, hour, hour + 1))
                        else:
                            #work.put(cmd)
                            if self.check_internet(cmd.cmd_info) == False:
                                self.send_error_mail('client disconnect', 'relay on fail')
                    except:
                        self.send_error_mail('client disconnect', str(sys.exc_info()))
                        print sys.exc_info()
                else:
                    work.put(cmd)
            
            elif cmd.cmd_type == 'reset_timer':
                hour = time.localtime().tm_hour
                work_count = work.qsize()
                if cmd.cmd_st == hour:
                    for i in range(work_count):
                        old_cmd = work.get()
                        if old_cmd.cmd_type != 'timer':
                            work.put(old_cmd)
                    self.addwork(self.sql_thread.read_timer())
                    work.put(Command('reset_timer', '', -1, 23))
                elif cmd.cmd_fin == hour:
                    for i in range(work_count):
                        work.get()
                    work.put(Command('reset_timer', '', 0, -1))
                elif cmd.cmd_info == 'now':
                    for i in range(work_count):
                        old_cmd = work.get()
                        if old_cmd.cmd_type != 'timer':
                            work.put(old_cmd)
                    self.addwork(self.sql_thread.read_timer())
                    work.put(Command('reset_timer', '', 0, 23))
                else:
                    work.put(cmd)
                    
            elif cmd.cmd_type =='update':
                self.sql_thread.updateDB(cmd.cmd_info)
                time.sleep(3)
                print 'updateDB done, rm %s' % cmd.cmd_info
                pexpect.run('sudo rm /home/pi/%s' % cmd.cmd_info)
                time.sleep(3)
            
            elif cmd.cmd_type =='test':
                self.sql_thread.scpDB()
            
        


        